package com.it.tydic.estate.common.util;

import com.github.pagehelper.util.StringUtil;
import com.it.tydic.estate.module.model.inner.TbWxglDjb;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

public class WxglExcel {

    public static void createHfglExcel(List<TbWxglDjb> list, HttpServletResponse response, String excelName, String excelType, String excelURL) throws  Exception {

        Workbook workbook = null;
        Sheet sheet =null;

        if (("0").equals(excelType)){
            String fileName = excelURL+"bdqk2003.xls";
            FileInputStream bfile = new FileInputStream(new File(fileName.trim()));
            workbook = new HSSFWorkbook(bfile); // 2003
            sheet = workbook.getSheet("报单情况");//
        }else{
            String fileName ="";
            fileName =excelURL+"bdqk2007.xlsx";
            FileInputStream file = new FileInputStream(new File(fileName.trim()));
            workbook = new XSSFWorkbook(file); //  2007
            sheet = workbook.getSheet("报单情况");//
        }
        List<String> keyList=createTitle(workbook);/////第1
        //新增数据行
        Row row = null;
        Cell cell = null;
        CellStyle cellStyle = workbook.createCellStyle();
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        int x=3;  //行
        if(x != 3){
            x++;
        }
       if(list.size() > 0){
           int xhao = 1;
            for (TbWxglDjb wxglDjb: list){
                row = sheet.createRow(x);//加一行
                row.setHeightInPoints(41);//行高度
                Map<String,Object> zlMap= getHfMap(wxglDjb,xhao);//第2 - - 放入数据
                setCellValueByKeyList(keyList,zlMap,row,cell,workbook,cellStyle);//第3
                int size=zlMap.size();
                x++;
                xhao++;
            }
        }

        if(StringUtil.isEmpty(excelName)){
            excelName=System.currentTimeMillis()+"";
        }
        String fileName ="";
        if("0".equals(excelType)){
            fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+excelName+".xls";
        }else {
            fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+excelName+".xlsx";
        }
        // 浏览器下载excel文件
        buildExcelDocument(fileName, workbook, response);//第4
    }

    /**
     * //第1
     */
    public static  List<String> createTitle(Workbook workbook){
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short)8);
        font.setBold(true);
        style.setFont(font);

        List<String> keyList=new ArrayList<String>();
        keyList.add("xuhao");//序号
        keyList.add("glcmc");//管理处名称
        keyList.add("bdlsh");//报单流水号
        keyList.add("bdsj");//报单时间
        keyList.add("bdfsName");//报单方式
        keyList.add("nr");//内容
        keyList.add("fjbhmc");//房间编号
        keyList.add("pg");//派工
        keyList.add("wg");//完工
        keyList.add("hf");//回访
        keyList.add("hfqk");//回访情况
        keyList.add("khyj");//客户意见
        keyList.add("khmydName");//客户满意度
        keyList.add("hfrq");//回访日期
        keyList.add("hfbz");//回访备注
        keyList.add("hfczry");//回访操作人员
        keyList.add("hfczsj");//回访操作时间

        keyList.add("wgqk");//完工情况
        keyList.add("wgrq");//完工日期
        keyList.add("clfy");//材料费用
        keyList.add("fwtc");//服务提成
        keyList.add("qtcb");//其它成本
        keyList.add("bdyl");//本单盈利
        keyList.add("hjfy");//合计收费
        keyList.add("wgbz");//完工备注
        keyList.add("wgczry");//完工操作人
        keyList.add("wgczsj");//完工操作时间
        keyList.add("wxdwName");//维修单位
        keyList.add("wxr");//维修人员
        keyList.add("pgczry");//派工操作人
        keyList.add("pgczsj");//派工操作时间

        keyList.add("lxr");//联系人
        keyList.add("lxdh");//联系电话
        keyList.add("yysj");//预约时间
        keyList.add("ecwx");//	二次维修
        keyList.add("wxlbName");//维修类别
        keyList.add("khlbName");//客户类别
        keyList.add("bdbz");//报单备注
        keyList.add("bdczry");//报单操作人员
        keyList.add("bdczsj");//报单操作时间
        return keyList;
    }
    /**
     * //第2，放入数据
     */
    public static Map<String,Object> getHfMap(TbWxglDjb wxglDjb,int xuhao){
        Map<String,Object> wxglMap=new LinkedHashMap<String,Object>();
        wxglMap.put("xuhao",xuhao);//xuhao
        wxglMap.put("glcmc",wxglDjb.getGlcmc());//管理处名称
        wxglMap.put("bdlsh",wxglDjb.getBdlsh());//报单流水号
        wxglMap.put("bdsj",wxglDjb.getBdrq());//报单时间
        wxglMap.put("bdfsName",wxglDjb.getBdfsName());//报单方式
        wxglMap.put("nr",wxglDjb.getNr());//内容
        wxglMap.put("fjbhmc",wxglDjb.getFjbhmc());//房间编号
        wxglMap.put("pg",wxglDjb.getPg());//派工
        wxglMap.put("wg",wxglDjb.getWg());//完工
        wxglMap.put("hf",wxglDjb.getHf());//回访
        wxglMap.put("hfqk",wxglDjb.getHfqk());//回访情况
        wxglMap.put("khyj",wxglDjb.getKhyj());//客户意见
        wxglMap.put("khmydName",wxglDjb.getKhmydName());//客户满意度
        wxglMap.put("hfrq",wxglDjb.getHfrq());//回访日期
        wxglMap.put("hfbz",wxglDjb.getHfbz());//回访备注
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String hfczsj = "";
        if(null!=wxglDjb.getHfczsj()){
            hfczsj = sdf.format(wxglDjb.getHfczsj());///////
        }
        wxglMap.put("hfczry",wxglDjb.getHfczry());//回访操作人员
        wxglMap.put("hfczsj",hfczsj);//回访操作时间
        wxglMap.put("wgqk",wxglDjb.getWgqk());//完工情况
        wxglMap.put("wgrq",wxglDjb.getWgrq());//完工日期
        wxglMap.put("clfy",wxglDjb.getWlfy());//材料费用
        wxglMap.put("fwtc",wxglDjb.getFwtc());//服务提成
        wxglMap.put("qtcb",wxglDjb.getQtcb());//其它成本
        wxglMap.put("bdyl",wxglDjb.getBdyl());//本单盈利
        wxglMap.put("hjfy",wxglDjb.getHjfy());//合计收费
        wxglMap.put("wgbz",wxglDjb.getWgbz());//完工备注
        wxglMap.put("wgczry",wxglDjb.getWgczry());//完工操作人
        String wgczsj ="";
        if(null!=wxglDjb.getWgczsj()){
            wgczsj = sdf.format(wxglDjb.getWgczsj());/////////
        }
        wxglMap.put("wgczsj",wgczsj);//完工操作时间
        wxglMap.put("wxdwName",wxglDjb.getWxdwName());//维修单位
        wxglMap.put("wxr",wxglDjb.getWxr());//维修人员
        wxglMap.put("pgczry",wxglDjb.getPgczry());//派工操作人
        String pgczsj ="";
        if(null!=wxglDjb.getPgczsj()){
            pgczsj = sdf.format(wxglDjb.getPgczsj());//////
        }
        wxglMap.put("pgczsj",pgczsj);//派工操作时间
        wxglMap.put("lxr",wxglDjb.getLxr());//联系人
        wxglMap.put("lxdh",wxglDjb.getLxdh());//联系电话
        wxglMap.put("yysj",wxglDjb.getYysj());//预约时间
        wxglMap.put("wxlbName",wxglDjb.getWxlbName());//维修类别
        wxglMap.put("khlbName",wxglDjb.getKhlbName());//客户类别
        wxglMap.put("ecwx",wxglDjb.getEcwx());//二次维修
        wxglMap.put("bdbz",wxglDjb.getBdbz());//报单备注
        wxglMap.put("bdczry",wxglDjb.getBdczry());//报单操作人员
        String bdczsj ="";
        if(null!=wxglDjb.getBdczsj()){
            bdczsj = sdf.format(wxglDjb.getBdczsj());//////
        }
        wxglMap.put("bdczsj",bdczsj);//报单操作时间
        return wxglMap;
    }

    public static void createWxtjExcel(List<TbWxglDjb> list, HttpServletResponse response, String excelName, String excelType, String excelURL) throws  Exception {
        Workbook workbook = null;
        Sheet sheet =null;

        if (("0").equals(excelType)){
            String fileName = excelURL+"wxtj2003.xls";
            FileInputStream file = new FileInputStream(new File(fileName.trim()));
            workbook = new HSSFWorkbook(file); // 2003
            sheet = workbook.getSheet("维修数量统计");//
        }else{
            String fileName ="";
            fileName =excelURL+"wxtj2007.xlsx";
            FileInputStream file = new FileInputStream(new File(fileName.trim()));
            workbook = new XSSFWorkbook(file); //  2007
            sheet = workbook.getSheet("维修数量统计");//
        }
        List<String> keyList=createWxtjTitle(workbook);/////第1
        //新增数据行
        Row row = null;
        Cell cell = null;
        CellStyle cellStyle = workbook.createCellStyle();
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        int x=3;  //行
        if(x != 3){
            x++;
        }
        if(list.size() > 0){
            int xhao = 1;
            for (TbWxglDjb wxglDjb: list){
                row = sheet.createRow(x);//加一行
                row.setHeightInPoints(40);//行高度
                Map<String,Object> zlMap= getWxtjMap(wxglDjb,xhao);//第2 - - 放入数据
                setCellValueByKeyList(keyList,zlMap,row,cell,workbook,cellStyle);//第3
                int size=zlMap.size();
                x++;
                xhao++;
            }
        }
        if(StringUtil.isEmpty(excelName)){
            excelName=System.currentTimeMillis()+"";
        }
        String fileName ="";
        if("0".equals(excelType)){
            fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+excelName+".xls";
        }else {
            fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+excelName+".xlsx";
        }
        // 浏览器下载excel文件
        buildExcelDocument(fileName, workbook, response);//第4
    }
    /**
     * //第1 设置 维修统计 key
     */
    public static  List<String> createWxtjTitle(Workbook workbook){
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short)8);
        font.setBold(true);
        style.setFont(font);
        List<String> keyWxtjList=new ArrayList<String>();
        keyWxtjList.add("xuhao");//序号
        keyWxtjList.add("glcmc");//管理处名称
        keyWxtjList.add("hj");//总数
        keyWxtjList.add("wpg");//未派工
        keyWxtjList.add("pg");//已派工
        keyWxtjList.add("wg");//已完工
        keyWxtjList.add("hf");//已回访
        return keyWxtjList;
    }
    /**
     * //第2，放入数据
     */
    public static Map<String,Object> getWxtjMap(TbWxglDjb wxglDjb,int xuhao){
        Map<String,Object> wxglMap=new LinkedHashMap<String,Object>();
        wxglMap.put("xuhao",xuhao);//xuhao
        wxglMap.put("glcmc",wxglDjb.getGlcmc());//管理处名称
        wxglMap.put("hj",wxglDjb.getHj());//总数
        wxglMap.put("wpg",wxglDjb.getWpg());//未派工
        wxglMap.put("pg",wxglDjb.getPg());//已派工
        wxglMap.put("wg",wxglDjb.getWg());//已完工
        wxglMap.put("hf",wxglDjb.getHf());//已回访
        return wxglMap;
    }

    /**
     * 第3，遍历list
     */
    private static void setCellValueByKeyList(List<String> keyList, Map<String, Object> zlMap, Row row,Cell cell,Workbook workbook, CellStyle cellStyle) {
        //设置背景颜色
        /*style.setFillPattern(FillPatternType.SOLID_FOREGROUND);*/
        int size=0;
        for(String key : keyList){
            cell = row.createCell(size);
            cell.setCellStyle(cellStyle);
            Object value=zlMap.get(key);
            if(value != null){
                cell.setCellValue(value.toString());
            }else{
                cell.setCellValue("");
            }
            size++;
        }
    }

    //浏览器下载excel文件
    public  static void buildExcelDocument(String filename,Workbook workbook,HttpServletResponse response) throws Exception{
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(filename,"utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }


}
